(Taken from the
FAQ in Postgres wiki)
“What do you think of the Postgres logo?”
fictional mother asked her fictionnal teenager
daughter.
“The color is a little insipid but it's fine,”
the fictionnal daughter replied.
“Don't you think it feels a little old and outdated?
”
the fictionnal mother insisted.
“Well, it's not a community of youngsters either,”
the fictionnal daughter explained.
Postgres is most of the time good with error messages
$ pg_dump --format=directory
--file=test -verbose --jobs 5
--dbname=mydb
pg_dump: last built-in OID is 16383
pg_dump: error:
no matching extensions were
found
$ export PGPASSWORD="******"
$ sudo -u postgres psql -d myDb -w
--no-password -t
-c "SELECT id FROM radusers WHERE id=1"
psql: fe_sendauth: no password supplied
create or replace function allRelevantTeas()
returns table(tea_id integer) as
$allRelevantTeas$
declare
result refcursor;
stmt text;
countries_with_tea text[] := array[
'england',
'turkey',
'india',
'japan',
'china'];
begin
stmt := '';
for tea_drinker in countries_with_tea loop
stmt := stmt ||
format($$(select tea_id from %I)$$, tea_drinker);
if tea_drinker <> 'china' then
stmt := stmt || $$ union $$;
end if;
end loop;
open result for execute stmt;
return result;
end
$allRelevantTeas$
language plpgsql stable;
select * from allRelevantTeas();
syntax error at or near "countries_with_tea"
create or replace function allRelevantTeas()
returns table(tea_id integer) as
$allRelevantTeas$
declare
result refcursor;
stmt text;
countries_with_tea text[] := array[
'england',
'turkey',
'india',
'japan',
'china'];
begin
stmt := '';
for tea_drinker in countries_with_tea loop
stmt := stmt ||
format($$(select tea_id from %I)$$, tea_drinker);
if tea_drinker <> 'china' then
stmt := stmt || $$ union $$;
end if;
end loop;
open result for execute stmt;
return result;
end
$allRelevantTeas$
language plpgsql stable;
for tea_drinker in countries_with_tea loop
foreach tea_drinker in countries_with_tea loop
/* Use psql, the best Postgres client in the world */
\set ON_ERROR_STOP on
/* Making the script idempotent */
drop schema if exists aoc25 cascade;
create schema aoc25;
set search_path to 'aoc25';
/* Insert data into table */
create table input (
id integer generated always as identity primary key,
data text not null
);
\copy input(data) from 'input.csv';
create function snafuToDec(snafu text) returns bigint as
$snafuToDec$
with snafu(num, l) as (
select string_to_array(snafu, null),
char_length(snafu)
)
select sum(
(case when element = '-' then '-1'
when element = '=' then '-2'
else element
end)::bigint
*
power(5, l-idx)
)
from snafu, unnest(num) with ordinality as a(element, idx);
$snafuToDec$ immutable language sql;
alter table input add column decNumber bigint generated always as (
snafuToDec(data)) stored;
create table power5 (num bigint, pow integer);
insert into power5 (num,pow) (
select power(5,n), n
from generate_series(1,26) t(n)
);
create function pow5(myNumber bigint) returns text as
$pow5$
with recursive processignpow5(x, num, pow, div, remain, snafu) as (
(
/* Get the highest power of 5 */
select myNumber as x,
num,
pow,
myNumber/num as div,
myNumber%num as remain,
(myNumber/num)::text as snafu
from power5
where myNumber/num > 0
order by num desc
limit 1)
union all
(
select x,
power5.num,
power5.pow,
processignpow5.remain/power5.num as div,
processignpow5.remain%power5.num as remain,
processignpow5.snafu || (processignpow5.remain/power5.num)::text
from processignpow5
inner join power5
on processignpow5.pow-1 = power5.pow)
)
/* To make the snafu conversion easier, let's add a 0 in the front */
select '0' || snafu || remain::text
from processignpow5
order by num
limit 1
;
$pow5$ language sql;
create function snafu(mydec5 text) returns text as
$snafu$
with recursive snafu(n, pow5, snafu) as (
select 1 as n,
/* If we have a number over 2 as a last digit, then we need to increase
* the "new" last digit.
* By increasing that number we might reach 5, which is bad, obvisouly as
* this is a number on base 5.
* So, we then need to increate the next digit and put 0 instead as a last
* digit.
* That way, we should never get 5 in the snafu digit */
case when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) > 3
/* Now we can safely take into account what happens should we have
* a 5 digit as a last number in our array */
then
case
when pow5[array_length(pow5,1)-1] = '4'
then pow5[1:array_length(pow5,1)-3] || (pow5[array_length(pow5,1)-2]::int+1)::text || array['0']
else pow5[1:array_length(pow5,1)-2] || (pow5[array_length(pow5,1)-1]::int+1)::text
end
/* We know we will never get 5 as the digit last digit shouldthe array
* length be 1 because we on purpose added a 0 in front of the base
* 5 number we had converted. */
when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) = 2
then array[pow5[1]] || (pow5[2]::int+1)::text
when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) = 3
then
case
when pow5[array_length(pow5,1)-1] = '4'
then array[pow5[1]] || (pow5[2]::int+1)::text || array['0']
else array[pow5[1]] || (pow5[2]::int+1)::text || pow5[3]
end
else pow5[1:array_length(pow5,1)-1]
end as pow5,
case when pow5[array_length(pow5,1)]::int < 3 then pow5[array_length(pow5,1)]
when pow5[array_length(pow5,1)]::int = 3 then '='
when pow5[array_length(pow5,1)]::int = 4 then '-'
end as snafu
from (select string_to_array('013140400422344032342', null)) as finalpow5(pow5)
union all
select n+1,
case when pow5[array_length(pow5,1)]::int > 2
then pow5[1:array_length(pow5,1)-2] || (pow5[array_length(pow5,1)-1]::int+1)::text
else pow5[1:array_length(pow5,1)-1]
end as pow5,
(case when pow5[array_length(pow5,1)]::int < 3 then pow5[array_length(pow5,1)]
when pow5[array_length(pow5,1)]::int = 3 then '='
when pow5[array_length(pow5,1)]::int = 4 then '-'
/* Due to the treatment from before round, we can end up with 5, so we
* need to take that into account */
when pow5[array_length(pow5,1)]::int = 5 then '0'
end) || snafu.snafu as snafu
from snafu
where array_length(pow5,1) > 0
)
select
case when substring(snafu from 1 for 1) = '0'
then substring(snafu from 2)
else snafu
end as firstStar
from snafu
where snafu is not null
order by n desc
limit 1
;
$snafu$ language sql;
select snafu(pow5(sum(decNumber)::bigint)) as firstStar
from input;
laetitia=# \i solution.sql
psql:solution.sql:142:
ERROR: column "por5" does not exist
LINE 43:
then por5[1:array_length(pow5,1)-2] ||
(pow5[array_l...
^
HINT: Perhaps you meant to reference the column
"snafu.pow5" or the column "*SELECT* 1.pow5"
/* Use psql, the best Postgres client in the world */
\set ON_ERROR_STOP on
/* Making the script idempotent */
drop schema if exists aoc25 cascade;
create schema aoc25;
set search_path to 'aoc25';
/* Insert data into table */
create table input (
id integer generated always as identity primary key,
data text not null
);
\copy input(data) from 'input.csv';
create function snafuToDec(snafu text) returns bigint as
$snafuToDec$
with snafu(num, l) as (
select string_to_array(snafu, null),
char_length(snafu)
)
select sum(
(case when element = '-' then '-1'
when element = '=' then '-2'
else element
end)::bigint
*
power(5, l-idx)
)
from snafu, unnest(num) with ordinality as a(element, idx);
$snafuToDec$ immutable language sql;
alter table input add column decNumber bigint generated always as (
snafuToDec(data)) stored;
create table power5 (num bigint, pow integer);
insert into power5 (num,pow) (
select power(5,n), n
from generate_series(1,26) t(n)
);
create function pow5(myNumber bigint) returns text as
$pow5$
with recursive processignpow5(x, num, pow, div, remain, snafu) as (
(
/* Get the highest power of 5 */
select myNumber as x,
num,
pow,
myNumber/num as div,
myNumber%num as remain,
(myNumber/num)::text as snafu
from power5
where myNumber/num > 0
order by num desc
limit 1)
union all
(
select x,
power5.num,
power5.pow,
processignpow5.remain/power5.num as div,
processignpow5.remain%power5.num as remain,
processignpow5.snafu || (processignpow5.remain/power5.num)::text
from processignpow5
inner join power5
on processignpow5.pow-1 = power5.pow)
)
/* To make the snafu conversion easier, let's add a 0 in the front */
select '0' || snafu || remain::text
from processignpow5
order by num
limit 1
;
$pow5$ language sql;
create function snafu(mydec5 text) returns text as
$snafu$
with recursive snafu(n, pow5, snafu) as (
select 1 as n,
/* If we have a number over 2 as a last digit, then we need to increase
* the "new" last digit.
* By increasing that number we might reach 5, which is bad, obvisouly as
* this is a number on base 5.
* So, we then need to increate the next digit and put 0 instead as a last
* digit.
* That way, we should never get 5 in the snafu digit */
case when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) > 3
/* Now we can safely take into account what happens should we have
* a 5 digit as a last number in our array */
then
case
when pow5[array_length(pow5,1)-1] = '4'
then por5[1:array_length(pow5,1)-3] || (pow5[array_length(pow5,1)-2]::int+1)::text || array['0']
else pow5[1:array_length(pow5,1)-2] || (pow5[array_length(pow5,1)-1]::int+1)::text
end
/* We know we will never get 5 as the digit last digit shouldthe array
* length be 1 because we on purpose added a 0 in front of the base
* 5 number we had converted. */
when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) = 2
then array[pow5[1]] || (pow5[2]::int+1)::text
when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) = 3
then
case
when pow5[array_length(pow5,1)-1] = '4'
then array[pow5[1]] || (pow5[2]::int+1)::text || array['0']
else array[pow5[1]] || (pow5[2]::int+1)::text || pow5[3]
end
else pow5[1:array_length(pow5,1)-1]
end as pow5,
case when pow5[array_length(pow5,1)]::int < 3 then pow5[array_length(pow5,1)]
when pow5[array_length(pow5,1)]::int = 3 then '='
when pow5[array_length(pow5,1)]::int = 4 then '-'
end as snafu
from (select string_to_array('013140400422344032342', null)) as finalpow5(pow5)
union all
select n+1,
case when pow5[array_length(pow5,1)]::int > 2
then por5[1:array_length(pow5,1)-2] || (pow5[array_length(pow5,1)-1]::int+1)::text
else pow5[1:array_length(pow5,1)-1]
end as pow5,
(case when pow5[array_length(pow5,1)]::int < 3 then pow5[array_length(pow5,1)]
when pow5[array_length(pow5,1)]::int = 3 then '='
when pow5[array_length(pow5,1)]::int = 4 then '-'
/* Due to the treatment from before round, we can end up with 5, so we
* need to take that into account */
when pow5[array_length(pow5,1)]::int = 5 then '0'
end) || snafu.snafu as snafu
from snafu
where array_length(pow5,1) > 0
)
select
case when substring(snafu from 1 for 1) = '0'
then substring(snafu from 2)
else snafu
end as firstStar
from snafu
where snafu is not null
order by n desc
limit 1
;
$snafu$ language sql;
select snafu(pow5(sum(decNumber)::bigint)) as firstStar
from input;
-- new table bloat query
-- still needs work; is often off by +/- 20%
WITH constants AS (
-- define some constants for sizes of things
-- for reference down the query and easy maintenance
SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 8 AS ma
),
no_stats AS (
-- screen out table who have attributes
-- which dont have stats, such as JSON
SELECT table_schema, table_name,
n_live_tup::numeric as est_rows,
pg_table_size(relid)::numeric as table_size
FROM information_schema.columns
JOIN pg_stat_user_tables as psut
ON table_schema = psut.schemaname
AND table_name = psut.relname
LEFT OUTER JOIN pg_stats
ON table_schema = pg_stats.schemaname
AND table_name = pg_stats.tablename
AND column_name = attname
WHERE attname IS NULL
AND table_schema NOT IN ('pg_catalog', 'information_schema')
GROUP BY table_schema, table_name, relid, n_live_tup
),
null_headers AS (
-- calculate null header sizes
-- omitting tables which dont have complete stats
-- and attributes which aren't visible
SELECT
hdr+1+(sum(case when null_frac <> 0 THEN 1 else 0 END)/8) as nullhdr,
SUM((1-null_frac)*avg_width) as datawidth,
MAX(null_frac) as maxfracsum,
schemaname,
tablename,
hdr, ma, bs
FROM pg_stats CROSS JOIN constants
LEFT OUTER JOIN no_stats
ON schemaname = no_stats.table_schema
AND tablename = no_stats.table_name
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
AND no_stats.table_name IS NULL
AND EXISTS ( SELECT 1
FROM information_schema.columns
WHERE schemaname = columns.table_schema
AND tablename = columns.table_name )
GROUP BY schemaname, tablename, hdr, ma, bs
),
data_headers AS (
-- estimate header and row size
SELECT
ma, bs, hdr, schemaname, tablename,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM null_headers
),
table_estimates AS (
-- make estimates of how large the table should be
-- based on row and page size
SELECT schemaname, tablename, bs,
reltuples::numeric as est_rows, relpages * bs as table_bytes,
CEIL((reltuples*
(datahdr + nullhdr2 + 4 + ma -
(CASE WHEN datahdr%ma=0
THEN ma ELSE datahdr%ma END)
)/(bs-20))) * bs AS expected_bytes,
reltoastrelid
FROM data_headers
JOIN pg_class ON tablename = relname
JOIN pg_namespace ON relnamespace = pg_namespace.oid
AND schemaname = nspname
WHERE pg_class.relkind = 'r'
),
estimates_with_toast AS (
-- add in estimated TOAST table sizes
-- estimate based on 4 toast tuples per page because we dont have
-- anything better. also append the no_data tables
SELECT schemaname, tablename,
TRUE as can_estimate,
est_rows,
table_bytes + ( coalesce(toast.relpages, 0) * bs ) as table_bytes,
expected_bytes + ( ceil( coalesce(toast.reltuples, 0) / 4 ) * bs ) as expected_bytes
FROM table_estimates LEFT OUTER JOIN pg_class as toast
ON table_estimates.reltoastrelid = toast.oid
AND toast.relkind = 't'
),
table_estimates_plus AS (
-- add some extra metadata to the table data
-- and calculations to be reused
-- including whether we cant estimate it
-- or whether we think it might be compressed
SELECT current_database() as databasename,
schemaname, tablename, can_estimate,
est_rows,
CASE WHEN table_bytes > 0
THEN table_bytes::NUMERIC
ELSE NULL::NUMERIC END
AS table_bytes,
CASE WHEN expected_bytes > 0
THEN expected_bytes::NUMERIC
ELSE NULL::NUMERIC END
AS expected_bytes,
CASE WHEN expected_bytes > 0 AND table_bytes > 0
AND expected_bytes <= table_bytes
THEN (table_bytes - expected_bytes)::NUMERIC
ELSE 0::NUMERIC END AS bloat_bytes
FROM estimates_with_toast
UNION ALL
SELECT current_database() as databasename,
table_schema, table_name, FALSE,
est_rows, table_size,
NULL::NUMERIC, NULL::NUMERIC
FROM no_stats
),
bloat_data AS (
-- do final math calculations and formatting
select current_database() as databasename,
schemaname, tablename, can_estimate,
table_bytes, round(table_bytes/(1024^2)::NUMERIC,3) as table_mb,
expected_bytes, round(expected_bytes/(1024^2)::NUMERIC,3) as expected_mb,
round(bloat_bytes*100/table_bytes) as pct_bloat,
round(bloat_bytes/(1024::NUMERIC^2),2) as mb_bloat,
table_bytes, expected_bytes, est_rows
FROM table_estimates_plus
)
-- filter output for bloated tables
SELECT databasename, schemaname, tablename,
can_estimate,
est_rows,
pct_bloat, mb_bloat,
table_mb
FROM bloat_data
-- this where clause defines which tables actually appear
-- in the bloat chart
-- example below filters for tables which are either 50%
-- bloated and more than 20mb in size, or more than 25%
-- bloated and more than 4GB in size
WHERE ( pct_bloat >= 50 AND mb_bloat >= 10 )
OR ( pct_bloat >= 25 AND mb_bloat >= 1000 )
ORDER BY mb_bloat DESC;
-- btree index stats query
-- estimates bloat for btree indexes
WITH btree_index_atts AS (
SELECT nspname,
indexclass.relname as index_name,
indexclass.reltuples,
indexclass.relpages,
indrelid, indexrelid,
indexclass.relam,
tableclass.relname as tablename,
regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,
indexrelid as index_oid
FROM pg_index
JOIN pg_class AS indexclass ON pg_index.indexrelid = indexclass.oid
JOIN pg_class AS tableclass ON pg_index.indrelid = tableclass.oid
JOIN pg_namespace ON pg_namespace.oid = indexclass.relnamespace
JOIN pg_am ON indexclass.relam = pg_am.oid
WHERE pg_am.amname = 'btree' and indexclass.relpages > 0
AND nspname NOT IN ('pg_catalog','information_schema')
),
index_item_sizes AS (
SELECT
ind_atts.nspname, ind_atts.index_name,
ind_atts.reltuples, ind_atts.relpages, ind_atts.relam,
indrelid AS table_oid, index_oid,
current_setting('block_size')::numeric AS bs,
8 AS maxalign,
24 AS pagehdr,
CASE WHEN max(coalesce(pg_stats.null_frac,0)) = 0
THEN 2
ELSE 6
END AS index_tuple_hdr,
sum( (1-coalesce(pg_stats.null_frac, 0)) * coalesce(pg_stats.avg_width, 1024) ) AS nulldatawidth
FROM pg_attribute
JOIN btree_index_atts AS ind_atts ON pg_attribute.attrelid = ind_atts.indexrelid AND pg_attribute.attnum = ind_atts.attnum
JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname
-- stats for regular index columns
AND ( (pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_catalog.pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE))
-- stats for functional indexes
OR (pg_stats.tablename = ind_atts.index_name AND pg_stats.attname = pg_attribute.attname))
WHERE pg_attribute.attnum > 0
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
),
index_aligned_est AS (
SELECT maxalign, bs, nspname, index_name, reltuples,
relpages, relam, table_oid, index_oid,
coalesce (
ceil (
reltuples * ( 6
+ maxalign
- CASE
WHEN index_tuple_hdr%maxalign = 0 THEN maxalign
ELSE index_tuple_hdr%maxalign
END
+ nulldatawidth
+ maxalign
- CASE /* Add padding to the data to align on MAXALIGN */
WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
ELSE nulldatawidth::integer%maxalign
END
)::numeric
/ ( bs - pagehdr::NUMERIC )
+1 )
, 0 )
as expected
FROM index_item_sizes
),
raw_bloat AS (
SELECT current_database() as dbname, nspname, pg_class.relname AS table_name, index_name,
bs*(index_aligned_est.relpages)::bigint AS totalbytes, expected,
CASE
WHEN index_aligned_est.relpages <= expected
THEN 0
ELSE bs*(index_aligned_est.relpages-expected)::bigint
END AS wastedbytes,
CASE
WHEN index_aligned_est.relpages <= expected
THEN 0
ELSE bs*(index_aligned_est.relpages-expected)::bigint * 100 / (bs*(index_aligned_est.relpages)::bigint)
END AS realbloat,
pg_relation_size(index_aligned_est.table_oid) as table_bytes,
stat.idx_scan as index_scans
FROM index_aligned_est
JOIN pg_class ON pg_class.oid=index_aligned_est.table_oid
JOIN pg_stat_user_indexes AS stat ON index_aligned_est.index_oid = stat.indexrelid
),
format_bloat AS (
SELECT dbname as database_name, nspname as schema_name, table_name, index_name,
round(realbloat) as bloat_pct, round(wastedbytes/(1024^2)::NUMERIC) as bloat_mb,
round(totalbytes/(1024^2)::NUMERIC,3) as index_mb,
round(table_bytes/(1024^2)::NUMERIC,3) as table_mb,
index_scans
FROM raw_bloat
)
-- final query outputting the bloated indexes
-- change the where and order by to change
-- what shows up as bloated
SELECT *
FROM format_bloat
WHERE ( bloat_pct > 50 and bloat_mb > 10 )
ORDER BY bloat_pct DESC;
WITH server_permissions AS (
SELECT
r.rolname,
'Server_Permissions' AS "Level",
r.rolsuper,
r.rolinherit,
r.rolcreaterole,
r.rolcreatedb,
r.rolcanlogin,
ARRAY(
SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON m.roleid = b.oid
WHERE m.member = r.oid
) AS memberof,
r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
),
db_ownership AS (
SELECT
r.rolname,
'DB_Ownership' AS "Level",
d.datname
FROM pg_catalog.pg_database d, pg_catalog.pg_roles r
WHERE d.datdba = r.oid
),
schema_permissions AS (
SELECT
'Schema Permissions' AS "Level",
r.rolname AS role_name,
nspname AS schema_name,
pg_catalog.has_schema_privilege(r.rolname, nspname, 'CREATE') AS create_grant,
pg_catalog.has_schema_privilege(r.rolname, nspname, 'USAGE') AS usage_grant
FROM pg_namespace pn, pg_catalog.pg_roles r
WHERE array_to_string(nspacl, ',') LIKE '%' || r.rolname || '%'
AND nspowner > 1
),
table_ownership AS (
SELECT
'Table Ownership' AS "Level",
tableowner,
schemaname,
tablename
FROM pg_tables
GROUP BY tableowner, schemaname, tablename
),
object_permissions AS (
SELECT
'Object Permissions' AS "Level",
COALESCE(NULLIF(s[1], ''), 'public') AS rolname,
n.nspname,
relname,
CASE
WHEN relkind = 'm' THEN 'Materialized View'
WHEN relkind = 'p' THEN 'Partitioned Table'
WHEN relkind = 'S' THEN 'Sequence'
WHEN relkind = 'I' THEN 'Partitioned Index'
WHEN relkind = 'v' THEN 'View'
WHEN relkind = 'i' THEN 'Index'
WHEN relkind = 'c' THEN 'Composite Type'
WHEN relkind = 't' THEN 'TOAST table'
WHEN relkind = 'r' THEN 'Table'
WHEN relkind = 'f' THEN 'Foreign Table'
END AS "Object Type",
s[2] AS privileges
FROM
pg_class c
JOIN pg_namespace n ON n.oid = relnamespace
JOIN pg_roles r ON r.oid = relowner,
UNNEST(COALESCE(relacl::text[], FORMAT('{%s=arwdDxt/%s}', rolname, rolname)::text[])) acl,
REGEXP_SPLIT_TO_ARRAY(acl, '=|/') s
WHERE relkind <> 'i' AND relkind <> 't'
)
SELECT
"Level",
rolname AS "Role",
'N/A' AS "Object Name",
'N/A' AS "Schema Name",
'N/A' AS "DB Name",
'N/A' AS "Object Type",
'N/A' AS "Privileges",
rolsuper::text AS "Is SuperUser",
rolinherit::text,
rolcreaterole::text,
rolcreatedb::text,
rolcanlogin::text,
memberof::text,
rolbypassrls::text
FROM server_permissions
UNION
SELECT
dow."Level",
dow.rolname,
'N/A',
'N/A',
datname,
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A'
FROM db_ownership AS dow
UNION
SELECT
"Level",
role_name,
'N/A',
schema_name,
'N/A',
'N/A',
CASE
WHEN create_grant IS TRUE AND usage_grant IS TRUE THEN 'Usage+Create'
WHEN create_grant IS TRUE AND usage_grant IS FALSE THEN 'Create'
WHEN create_grant IS FALSE AND usage_grant IS TRUE THEN 'Usage'
ELSE 'None'
END,
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A'
FROM schema_permissions
UNION
SELECT
"Level",
tableowner,
tablename,
schemaname,
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A'
FROM table_ownership
UNION
SELECT
"Level",
rolname,
relname,
nspname,
'N/A',
"Object Type",
privileges,
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A',
'N/A'
FROM object_permissions
ORDER BY "Role";